/**
 * 
 */
package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.Map;
import java.sql.SQLException;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.expert.AuthGroup;
import com.orange.modules.entity.expert.AuthQuestionnaire;
import com.orange.modules.entity.expert.AuthSchoolQuestionnaire;

/**
 * @author gaogs
 *
 *         2016-11-16
 */
@Repository
public class AuthQuestionnaireRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;

	@Autowired
	private AuthQuestionnaireDao authQuestionnaireDao;
	
	/**
	 * 根据账号查询问卷调查表
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public AuthQuestionnaire queryAuthQuestionnaireInfoBypid(String pid) throws SQLException {
		AuthQuestionnaire aq = authQuestionnaireDao.findOne(pid);
		return aq;
	}
	public AuthSchoolQuestionnaire AuthSchoolQuestionnaire(String pid) throws SQLException {
		String sql = "SELECT a.p_id AS pid,a.s_school_id AS schoolId,a.s_student_file_name AS studentFileName,"
				+ "a.s_student_file_url AS studentFileUrl,a.s_teacher_file_name AS teacherFileName,"
				+ "a.s_teacher_file_url AS teacherFileUrl,a.s_employer_file_name AS employerFileName,"
				+ "a.s_employer_file_url AS employerFileUrl,a.s_overall_satisfaction AS overallSatisfaction,"
				+ "a.s_update_time AS updateTime,a.s_create_time AS createTime,s.s_account AS account,"
				+ "s.s_school_name AS schoolname,s.s_major AS major "
				+ "FROM t_auth_questionnaire AS a LEFT JOIN t_auth_school AS s ON a.s_school_id = s.p_id WHERE a.p_id = ?";
		AuthSchoolQuestionnaire aq = dbHelper.getBean(sql, AuthSchoolQuestionnaire.class,pid);
		return aq;
	}
	/**
	 * 给问卷调查表插入及保存数据
	 * @param aq
	 * @throws SQLException
	 */
	public void updateAuthSchoolInfo(AuthQuestionnaire aq) throws SQLException{
		authQuestionnaireDao.save(aq);
	}
	
	/**
	 * 分页显示调查数据表
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthSchoolQuestionnaire> queryAuthQuestionnairePage(Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 a.p_id AS pid,a.s_school_id AS schoolId,a.s_student_file_name AS studentFileName,"
				+ "a.s_student_file_url AS studentFileUrl,a.s_teacher_file_name AS teacherFileName,"
				+ "a.s_teacher_file_url AS teacherFileUrl,a.s_employer_file_name AS employerFileName,"
				+ "a.s_employer_file_url AS employerFileUrl,a.s_overall_satisfaction AS overallSatisfaction,"
				+ "a.s_update_time AS updateTime,a.s_create_time AS createTime,s.s_account AS account,"
				+ "s.s_school_name AS schoolname,s.s_major AS major FROM t_auth_questionnaire AS a "
				+ "LEFT JOIN t_auth_school AS s ON a.s_school_id = s.p_id "
				+ "WHERE 1 = 1");
		if(StringUtils.isNotBlank(map.get("schoolId"))){
			sql.append(" AND a.s_school_id = '"+map.get("schoolId")+"'");
		}
		sql.append(" ORDER BY a.s_create_time DESC ");
		Page<AuthSchoolQuestionnaire> questionnaire = dbHelper.getPage(sql.toString(),AuthSchoolQuestionnaire.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return questionnaire;
	}
	
	/**
	 * 根据学校id查询问卷调查
	 */
	public AuthSchoolQuestionnaire queryAuthSchoolQuestionnaireBySchoolId(String schoolId) throws SQLException{
		String sql = "SELECT a.p_id AS pid,a.s_school_id AS schoolId,a.s_student_file_name AS studentFileName,"
				+ "a.s_student_file_url AS studentFileUrl,a.s_teacher_file_name AS teacherFileName,"
				+ "a.s_teacher_file_url AS teacherFileUrl,a.s_employer_file_name AS employerFileName,"
				+ "a.s_employer_file_url AS employerFileUrl,a.s_overall_satisfaction AS overallSatisfaction,"
				+ "a.s_update_time AS updateTime,a.s_create_time AS createTime,s.s_account AS account,"
				+ "s.s_school_name AS schoolname,s.s_major AS major "
				+ "FROM t_auth_questionnaire AS a LEFT JOIN t_auth_school AS s ON a.s_school_id = s.p_id WHERE a.s_school_id = ?";
		AuthSchoolQuestionnaire aq = dbHelper.getBean(sql, AuthSchoolQuestionnaire.class,schoolId);
		return aq;
	}

}
